﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OneMake.Model
{
    //运营数据
    public class report_motion_data
    {
        [MapToField(FieldName = "日期")]
        public DateTime stat_datetime { get; set; }

        [MapToField(FieldName = "店铺活动")]
        public string shop_hd { get; set; }

        [MapToField(FieldName = "聚划算活动")]
        public string jhs { get; set; }

        [MapToField(FieldName = "硬广位置")]
        public string broad_pos { get; set; }

        [MapToField(FieldName = "店铺UV")]
        public int shop_uv { get; set; }
         	
        [MapToField(FieldName = "店铺PV")]
        public int shop_pv { get; set; }

        [MapToField(FieldName = "平均访问深度")]
        public double per_pages { get; set; }

        [MapToField(FieldName = "人均店内停留时间（秒）")]
        public string stop_time { get; set; }
         	 
        [MapToField(FieldName = "首页UV")]
        public int home_uv { get; set; }
         	 
        [MapToField(FieldName = "首页PV")]
        public int home_pv { get; set; }
         	 
        [MapToField(FieldName = "类目流量")]
        public int type_pv { get; set; }
         	
        [MapToField(FieldName = "搜索流量")]
        public int search_pv { get; set; }
        	 
        [MapToField(FieldName = "聚划算流量")]
        public int jhs_pv { get; set; }
        	 
        [MapToField(FieldName = "淘宝收藏流量")]
        public int tb_sc { get; set; }     
    	 
        [MapToField(FieldName = "直通车流量")]
        public int ztc_pv { get; set; }
         	
        [MapToField(FieldName = "淘客流量")]
        public int tk_pv { get; set; }

        [MapToField(FieldName = "定价CPM")]
        public int price_cpm { get; set; }
        	
        [MapToField(FieldName = "品牌广告")]
        public int pp_gg { get; set; }

        [MapToField(FieldName = "富媒体广告")]
        public int media_gg { get; set; }
  
        [MapToField(FieldName = "钻石展位")]
        public int gg_pv_zs { get; set; }

        [MapToField(FieldName = "阿里旺旺广告")]
        public int alww_gg { get; set; }

        [MapToField(FieldName = "拍下笔数")]
        public int order_total_num { get; set; }
        	 
        [MapToField(FieldName = "成交用户数")]
        public int cj_user_num { get; set; }

        [MapToField(FieldName = "支付宝成交件数")]
        public int fk_item_num { get; set; }

        [MapToField(FieldName = "支付宝成交笔数")]
        public int fk_order_num { get; set; }

        [MapToField(FieldName = "拍下总金额")]
        public double order_total { get; set; }
	
        [MapToField(FieldName = "支付宝成交金额")]
        public double zfb_total { get; set; }

        [MapToField(FieldName = "当日拍下-支付率")]
        public string fk_rate { get; set; }
        	
        [MapToField(FieldName = "当日拍下-付款笔数")]
        public int day_fk_num { get; set; }
        
        [MapToField(FieldName = "当日拍下-付款金额")]
        public double day_fk_total { get; set; }

        [MapToField(FieldName = "全店成交转化率")]
        public string pv_zh_rate { get; set; }

        [MapToField(FieldName = "人均成交件数")]
        public double avg_items { get; set; }
        	 
        [MapToField(FieldName = "客单价")]
        public double avg_price { get; set; }

        [MapToField(FieldName = "直通车每日费用")]
        public double ztc_day_pay { get; set; }
        	
        [MapToField(FieldName = "直通车展现量")]
        public double ztc_show_num { get; set; }

        [MapToField(FieldName = "直通车点击量")]
        public double ztc_click_num { get; set; }

        [MapToField(FieldName = "直通车平均点击花费")]
        public double ztc_avg_click_pay { get; set; }

        [MapToField(FieldName = "直通车点击率")]
        public string ztc_click_rate { get; set; }

        [MapToField(FieldName = "硬广费用")]
        public double yg_pay { get; set; }

        [MapToField(FieldName = "每日总费用")]
        public double day_pay { get; set; }
    }

    partial class ReportHelper
    {
        private T getDbValue<T>(PetaPoco.Database db, string tableName, string fieldName, int shopId, DateTime day)
        {
            string sql = string.Format(@"select {0} from {1} where shopid=@0 and stat_datetime=@1", fieldName, tableName);
            return db.SafeExecuteScalar<T>(sql, shopId, day.ToShortDateString());
        }

        private T getDbValue_name<T>(PetaPoco.Database db, string tableName, string fieldName, int shopId, DateTime day)
        {
            string shopname = db.SafeExecuteScalar<string>(@"select name from conf_shop where shopid=@0", shopId);
            string sql = string.Format(@"select {0} from {1} where shopname=@0 and stat_datetime=@1", fieldName, tableName);
            return db.SafeExecuteScalar<T>(sql, shopname, day.ToShortDateString());
        }

        private T getDbValue_sum<T>(PetaPoco.Database db, string tableName, string fieldName, int shopId, DateTime day)
        {
            string sql = string.Format(@"select sum({0}) from {1} where shopid=@0 and stat_datetime=@1", fieldName, tableName);
            return db.SafeExecuteScalar<T>(sql, shopId, day.ToShortDateString());
        }

        public report_motion_data get_report_yyrb(int shopId, DateTime day)
        {
            report_motion_data report = new report_motion_data();
            var db = new PetaPoco.Database("mysql" + UserSession.Network);
            
            //日期	
            report.stat_datetime = day.Date;   

            //店铺活动
            report.shop_hd = getDbValue_name<string>(db, "jhs_d_yxhd", "activity", shopId, day);
            //聚划算活动
            report.jhs = getDbValue_name<string>(db, "jhs_d_yxhd", "jhs", shopId, day);
            //硬广位置
            report.broad_pos = getDbValue_name<string>(db, "jhs_d_yxhd", "pos", shopId, day);

            //店铺UV                            
            report.shop_uv = getDbValue<int>(db, "lz_d_innervisit", "uv", shopId, day);
            //店铺PV 
            report.shop_pv = getDbValue_sum<int>(db, "lz_d_shopop", "browses", shopId, day); 

            //平均访问深度
            report.per_pages = getDbValue<double>(db, "lz_d_innervisit", "avg_depth", shopId, day);  
            //人均店内停留时间（秒）
            report.stop_time = getDbValue_sum<string>(db, "lz_d_innervisit", "avg_time", shopId, day);

            //首页UV
            report.home_uv = getDbValue_sum<int>(db, "lz_d_homepage", "vists", shopId, day);
            //首页PV
            report.home_pv = getDbValue_sum<int>(db, "lz_d_homepage", "browses", shopId, day);
            
            //从 流量来源构成LZ_D_FLOWSOURCE 读取所需数据
            string sql = "select sum(browses) from lz_d_flowsource where detail like @0 and shopid=@1 and stat_datetime=@2";
            //类目流量
            report.type_pv = db.SafeExecuteScalar<int>(sql, "%淘宝类目%", shopId, day.ToShortDateString()) + db.SafeExecuteScalar<int>(sql, "%商城类目%", shopId, day.ToShortDateString());	 
            //搜索流量
            report.search_pv = db.SafeExecuteScalar<int>(sql, "%淘宝搜索%", shopId, day.ToShortDateString()) + db.SafeExecuteScalar<int>(sql, "%商城搜索%", shopId, day.ToShortDateString()); 
            //聚划算流量
            report.jhs_pv = db.SafeExecuteScalar<int>(sql, "%聚划算%", shopId, day.ToShortDateString()); 
            //淘宝收藏流量	
            report.tb_sc = db.SafeExecuteScalar<int>(sql, "%淘宝收藏%", shopId, day.ToShortDateString());
            //直通车流量
            report.ztc_pv = db.SafeExecuteScalar<int>(sql, "%直通车%", shopId, day.ToShortDateString());
            //淘客流量
            report.tk_pv = db.SafeExecuteScalar<int>(sql, "%淘客%", shopId, day.ToShortDateString());
            //定价CPM
            report.price_cpm = db.SafeExecuteScalar<int>(sql, "%定价CPM%", shopId, day.ToShortDateString());
            //品牌广告
            report.pp_gg = db.SafeExecuteScalar<int>(sql, "%品牌广告%", shopId, day.ToShortDateString());
            //富媒体广告
            report.media_gg = db.SafeExecuteScalar<int>(sql, "%富媒体广告%", shopId, day.ToShortDateString());
            //钻石展位
            report.gg_pv_zs = db.SafeExecuteScalar<int>(sql, "%钻石展位%", shopId, day.ToShortDateString());
	        //阿里旺旺广告
            report.alww_gg = db.SafeExecuteScalar<int>(sql, "%阿里旺旺广告%", shopId, day.ToShortDateString());   
    
            //拍下笔数
            report.order_total_num = getDbValue_sum<int>(db, "lz_d_shopop", "shopcart_num2", shopId, day);     
            //成交用户数	
            report.cj_user_num = getDbValue_sum<int>(db, "lz_m_shopop", "n_users", shopId, day);           
            //支付宝成交件数
            report.fk_item_num = getDbValue_sum<int>(db, "lz_m_shopop", "alipay_num1", shopId, day);

            //支付宝成交笔数
            report.fk_order_num = getDbValue<int>(db, "lz_m_shopop", "alipay_num2", shopId, day);         
            //拍下总金额
            report.order_total = getDbValue<double>(db, "lz_m_shopop", "avg_shopcart", shopId, day);       
            //支付宝成交金额
            report.zfb_total = getDbValue<int>(db, "lz_m_shopop", "per_alipay", shopId, day);

            //当日拍下-支付率
            report.fk_rate = string.Format("{0}%", Math.Round(getDbValue_sum<double>(db, "lz_m_shopop", "per_paid", shopId, day) * 100, 2).ToString("0.00"));        
            //当日拍下-付款笔数
            report.day_fk_num = getDbValue<int>(db, "lz_m_shopop", "day_paid_num", shopId, day);         
            //当日拍下-付款金额
            report.day_fk_total = getDbValue<double>(db, "lz_m_shopop", "day_paid_amount", shopId, day);
            //全店成交转化率
            report.pv_zh_rate = string.Format("{0}%", Math.Round(getDbValue_sum<double>(db, "lz_m_shopop", "per_return2", shopId, day) * 100, 2).ToString("0.00")); 
            //人均成交件数
            report.avg_items = getDbValue_sum<double>(db, "lz_m_shopop", "avg_num1", shopId, day);           
            //客单价
            report.avg_price = getDbValue_sum<double>(db, "lz_m_shopop", "uprice", shopId, day);               
       
            //直通车每日费用
            report.ztc_day_pay = getDbValue_sum<double>(db, "lz_d_ztcjb", "costs", shopId, day);               
            //直通车展现量
            report.ztc_show_num = getDbValue_sum<double>(db, "lz_d_ztcjb", "shows", shopId, day);               
            //直通车点击量
            report.ztc_click_num = getDbValue_sum<double>(db, "lz_d_ztcjb", "clicks", shopId, day);               
            //直通车平均点击花费
            report.ztc_avg_click_pay = getDbValue_sum<double>(db, "lz_d_ztcjb", "avg_click", shopId, day);  
            //直通车点击率
            if (report.ztc_show_num == 0.0)
                report.ztc_click_rate = "0.00%";
            else
                report.ztc_click_rate = string.Format("{0}%", Math.Round((double)report.ztc_click_num / report.ztc_show_num * 100, 2).ToString("0.00"));   
             
            //硬广费用
            report.yg_pay = getDbValue_sum<double>(db, "jhs_d_yxhd", "fee", shopId, day);               
            //每日总费用
            report.day_pay = report.ztc_day_pay + report.yg_pay;               

            return report;
        }

        public List<report_motion_data> get_report_yyrb(int shopId, DateTime beginTime, DateTime endTime)
        {
            if (beginTime.Date > endTime.Date)
            {
                System.Windows.Forms.MessageBox.Show("起始时间不能小于结束时间!");
                return null;
            }

            List<report_motion_data> reports = new List<report_motion_data>();
            DateTime tempTime = beginTime;
            do
            {
                report_motion_data report = get_report_yyrb(shopId, tempTime);
                if (report != null)
                    reports.Add(report);
                tempTime = tempTime.AddDays(1);
            }
            while (tempTime <= endTime);

            return reports;
        }
    }
}
